/// Create ID-level dataset of hourly starts and ends of other insurance coverage

********************************************************************************
***import data on coverage history 

use "data/inputs/hugo/all_insurance", clear

********************************************************************************
*** Get ID-level data merged with Hugo

destring subjectnbr, replace
bysort id policynbr: egen numsub = sum(subjectnbr)

gen startdate = date(subjectbegindate, "MDY")
gen enddate = date(subjectenddate, "MDY")
format startdate %td
format enddate %td

*get hugo collapsed action ready for merging
preserve
	use "data/cleaned/hugo/All balance actions", clear
	keep id timestamp_usercreated threemonth timestamp_bind bound vin days_eligible ///
		car_make car_model car_year cohort bundle_discount price meanpay
		
	duplicates drop 
	drop if mi(price)
	*clean timestamps
	gen day_usercreated = dofc(timestamp_usercreated)
	format day_usercreated %td
	gen day_threemo = dofc(threemonth)
	format day_threemo %td
	gen day_bind = dofc(timestamp_bind)
	format day_bind %td

	tempfile hugo 
	save `hugo'
	count
restore

*merge with hugo data
merge m:1 id using `hugo', nogen keep(2 3)

*we have 1,537 IDs at the id-policy-ambest level
unique id
isid id policynbr ambestnbr, missok

*save constraint and anyreg for hugo heterogeneity
preserve
	keep id anyreg anyregnonz everins neverins nevereg everlapse everlapsenone ///
		evercanc
	duplicates drop
	save "data/cleaned/hugo/hugo anyreg het", replace
restore


********************************************************************************
*** create ID-level dataset with start and end hours for policy history

*drop policies that end before Hugo
drop if enddate < day_usercreated
*drop policies that begin after Hugo end
drop if startdate > day_threemo
	
keep id timestamp_usercreated timestamp_bind startdate enddate length
order id timestamp_usercreated timestamp_bind startdate enddate length
gen date = startdate
format date %td

gen starttime = cofd(startdate)
format starttime %tc
gen endtime = cofd(enddate)
format endtime %tc

gen hourstart = (starttime - timestamp_usercreated)/(1000*60*60)
gen hourend = (endtime - timestamp_usercreated)/(1000*60*60)

keep id timestamp_usercreated timestamp_bind hourstart hourend
bysort id: gen pol=_n
reshape wide hourstart hourend, i(id timestamp_usercreated timestamp_bind) j(pol)

keep id hour*

save "data/cleaned/hugo/hourly any insurance", replace


beep
